import mysql.connector
import openpyxl

mydb = mysql.connector.connect(
    host="192.168.47.102",  # 数据库主机地址
    user="myuser",  # 数据库用户名
    port=3307,
    database="iridescent_study",  # 数据库名称 没有的话插入操作报错
    passwd="211314wccA@",  # 数据库密码
    # host="192.168.47.102",  # 数据库主机地址
    # port=3306,
    # user="ccmadmin",  # 数据库用户名
    # database="mh_xiyou",#数据库名称 没有的话插入操作报错
    # passwd="211314wccA@"  # 数据库密码

)

# print(mydb)
mycursor = mydb.cursor()
# mycursor.execute("SHOW DATABASES")
# mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

# 插入字典表
sql = "INSERT INTO egl_word_zhen_ti ( code,ch,group_id,type,re_status) VALUES (%s,'--',%s,'1', '0')"
#sql = "INSERT INTO zhen_ti_ju ( id,remark,remark2) VALUES (%s,%s,%s)"

# 插入角色
# sql = "INSERT INTO mh_role  VALUES (null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

# 插入种族基本属性
# sql = "INSERT INTO mh_base_attribute  VALUES (null,%s,%s,%s,%s,%s,%s)"

# val = ("RUNOOB", "https://www.runoob.com")
dict_id = 1001


workbook = openpyxl.load_workbook('tmp.xlsx')

sheet = workbook["Sheet6"]
sheet = workbook["Sheet10"]
# 获取最大列数
maxColumn = sheet.max_column
print('最大列', maxColumn)
max_row = sheet.max_row
print('最大行', max_row)

for one_column_data in sheet.iter_rows():
    row_data = []
    for cell in one_column_data:
        row_data.append(cell.value)
        # print(cell.value)
    val = tuple(row_data)
    try:
        mycursor.execute(sql, val)
        mydb.commit()
    except:
        print("出错")
    print(val)

# val = [
#   ('1001', 'https://www.google.com'),
#   ('1001', 'https://www.github.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('1001', 'https://www.taobao.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('stackoverflow', 'https://www.stackoverflow.com/')
# ]
# 插入单个

# 插入多条
# mycursor.executemany(sql, val)
